package com.lewei.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.lewei.model.PrintInModel;
import com.lewei.model.SgbRuleInfo;
import com.lewei.utils.Constants;
import com.lewei.print.*;
import com.lewei.view.MainPrint;

/*
 * 
 * 所需jar包
 * 下载地址http://www.apache.org/dyn/closer.cgi/poi/release/bin/poi-bin-3.9-20121203
 * .zip <br />
 * poi-3.7-20101029.jar poi-ooxml-3.7-20101029.jar<br />
 * poi-ooxml-schemas-3.7-20101029.jar xmlbeans-2.3.0.jar dom4j-1.6.1.jar
 * 
 * 
 */

/**
 * Excel操作类。 Excel导入规则数据
 */
public class ExcelSgbPrintRule {

	/** 六位年月日 */
	private static SimpleDateFormat sdf = new SimpleDateFormat("yyMMdd");

	/** 禁止实例化 */
	private ExcelSgbPrintRule() {
	};

	public static void main(String[] args) {

		File file = new File(Constants.RULEPATH);
		try {
			List<SgbRuleInfo> list = readExcel(file);
			for (int i = 0; i < list.size(); i++) {
				System.out.println(list.get(i).getType());
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 对外提供读取excel 的方法
	 */
	public static List<SgbRuleInfo> readExcel(File file) throws IOException {
		String fileName = file.getName();

		String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName
				.substring(fileName.lastIndexOf(".") + 1);
		// if ("xls".equals(extension)) {
		// return read2003Excel(file);
		// } else
		if ("xlsx".equals(extension)) {
			return read2007Excel(file);
		} else {
			throw new IOException("不是.xlsx文件!");
		}
	}

	/**
	 * 读取 office 2003 excel
	 * 
	 * @throws IOException
	 * @throws FileNotFoundException
	 */
	// @SuppressWarnings("deprecation")
	// private static List<SgbRuleInfo> read2003Excel(File file) throws
	// IOException {
	// List<SgbRuleInfo> list = new ArrayList<SgbRuleInfo>();
	// HSSFWorkbook hwb = new HSSFWorkbook(new FileInputStream(file));
	// HSSFSheet sheet = hwb.getSheetAt(0);
	// HSSFRow row = null;
	// HSSFCell cell = null;
	// for (int i = 2; i < sheet.getPhysicalNumberOfRows(); i++) {
	// row = sheet.getRow(i);
	// if (cell == null || cell.getStringCellValue().trim().equals("")) {
	// continue;
	// }
	//
	// // String sheetName = sheet.getSheetName();
	//
	// SgbRuleInfo SgbRuleInfo=new SgbRuleInfo();
	//
	//
	// String str = "";
	// // 型号
	// cell = row.getCell(0);
	// if (cell == null) {
	// continue;
	// }
	// cell.setCellType(CellType.STRING);
	// str = cell.getStringCellValue().trim();
	// SgbRuleInfo.setModelNo(str);
	// // 類型
	// cell = row.getCell(1);
	// // if (cell == null) {
	// // continue;
	// // }
	// cell.setCellType(CellType.STRING);
	// str = cell.getStringCellValue().trim();
	// SgbRuleInfo.setType(str);
	// //VNS
	// cell = row.getCell(2);
	// cell.setCellType(CellType.STRING);
	// str = cell.getStringCellValue().trim();
	// SgbRuleInfo.setVns(str);
	// //技術參數
	// cell = row.getCell(3);
	// cell.setCellType(CellType.STRING);
	// str = cell.getStringCellValue().trim();
	// SgbRuleInfo.setTechnicalParameters(str);
	// //VCS
	// cell = row.getCell(4);
	// cell.setCellType(CellType.STRING);
	// str = cell.getStringCellValue().trim();
	// SgbRuleInfo.setVcs(str);
	// //VTS
	// cell = row.getCell(5);
	// cell.setCellType(CellType.STRING);
	// str = cell.getStringCellValue().trim();
	// SgbRuleInfo.setVts(str);
	//
	// list.add(SgbRuleInfo);
	// }
	// hwb.close();
	// return list;
	// }

	/**
	 * 读取Office 2007 excel
	 */
	private static List<SgbRuleInfo> read2007Excel(File file) {

		try {
			List<SgbRuleInfo> list = new ArrayList<SgbRuleInfo>();
			// 构造 XSSFWorkbook 对象，strPath 传入文件路径
			XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));

//			MainPrint mainprint = new MainPrint();

//			if (mainprint.getGuideRail().contains("润滑")) {

				// 读取第一章表格内容
				XSSFSheet sheet = xwb.getSheetAt(0);

				XSSFRow row = null;
				XSSFCell cell = null;
				for (int i = 2; i <= sheet.getPhysicalNumberOfRows(); i++) {
					row = sheet.getRow(i);
					if (row == null) {
						continue;
					}
					SgbRuleInfo SgbRuleInfo = new SgbRuleInfo();
					String str = "";

					// sgbO5V15
					cell = row.getCell(0);
					// if (cell == null) {
					// continue;
					// }
					cell.setCellType(CellType.STRING);
					str = cell.getStringCellValue().trim();
					SgbRuleInfo.setSgbO5V15(str);

					// sgbO5V25
					cell = row.getCell(1);
					// if (cell == null) {
					// continue;
					// }
					cell.setCellType(CellType.STRING);
					str = cell.getStringCellValue().trim();
					SgbRuleInfo.setSgbO5V25(str);
					;

					// sgbO1V25
					cell = row.getCell(2);
					cell.setCellType(CellType.STRING);
					str = cell.getStringCellValue().trim();
					SgbRuleInfo.setSgbO1V25(str);

					// sgbO2V25
					cell = row.getCell(3);
					cell.setCellType(CellType.STRING);
					str = cell.getStringCellValue().trim();
					SgbRuleInfo.setSgbO2V25(str);

					// sgbO3V25
					cell = row.getCell(4);
					cell.setCellType(CellType.STRING);
					str = cell.getStringCellValue().trim();
					SgbRuleInfo.setSgbO3V25(str);

					// x
					cell = row.getCell(5);
					cell.setCellType(CellType.STRING);
					str = cell.getStringCellValue().trim();
					SgbRuleInfo.setX(str);
					
					// typeO
					cell = row.getCell(6);
					cell.setCellType(CellType.STRING);
					str = cell.getStringCellValue().trim();
					SgbRuleInfo.setTypeO(str);

					list.add(SgbRuleInfo);
				}
//			} else if (mainprint.getGuideRail().contains("干燥")) {

				// 读取第二章表格内容
				XSSFSheet sheet2 = xwb.getSheetAt(1);
				XSSFRow row2 = null;
				XSSFCell cell2 = null;
				for (int i = 2; i <= sheet2.getPhysicalNumberOfRows(); i++) {
					row2 = sheet2.getRow(i);
					if (row2 == null) {
						continue;
					}
					SgbRuleInfo SgbRuleInfo2 = new SgbRuleInfo();
					String str = "";

					// sgbD5V25
					cell2 = row2.getCell(0);
					// if (cell == null) {
					// continue;
					// }
					cell2.setCellType(CellType.STRING);
					str = cell2.getStringCellValue().trim();
					SgbRuleInfo2.setSgbD5V25(str);

					// sgbD1V25
					cell2 = row2.getCell(1);
					// if (cell == null) {
					// continue;
					// }
					cell2.setCellType(CellType.STRING);
					str = cell2.getStringCellValue().trim();
					SgbRuleInfo2.setSgbD1V25(str);
					;

					// sgbD1V30
					cell2 = row2.getCell(2);
					cell2.setCellType(CellType.STRING);
					str = cell2.getStringCellValue().trim();
					SgbRuleInfo2.setSgbD1V30(str);

					// sgbD1V35
					cell2 = row2.getCell(3);
					cell2.setCellType(CellType.STRING);
					str = cell2.getStringCellValue().trim();
					SgbRuleInfo2.setSgbD1V35(str);

					// sgbD2V15
					cell2 = row2.getCell(4);
					cell2.setCellType(CellType.STRING);
					str = cell2.getStringCellValue().trim();
					SgbRuleInfo2.setSgbD2V15(str);

					// sgbD2V25
					cell2 = row2.getCell(5);
					cell2.setCellType(CellType.STRING);
					str = cell2.getStringCellValue().trim();
					SgbRuleInfo2.setSgbD2V25(str);

					// sgbD2V35
					cell2 = row2.getCell(6);
					cell2.setCellType(CellType.STRING);
					str = cell2.getStringCellValue().trim();
					SgbRuleInfo2.setSgbD2V35(str);

					// sgbD3V25
					cell2 = row2.getCell(7);
					cell2.setCellType(CellType.STRING);
					str = cell2.getStringCellValue().trim();
					SgbRuleInfo2.setSgbD3V25(str);

					// sgbD3V35
					cell2 = row2.getCell(8);
					cell2.setCellType(CellType.STRING);
					str = cell2.getStringCellValue().trim();
					SgbRuleInfo2.setSgbD3V35(str);

					// x
					cell2 = row2.getCell(9);
					cell2.setCellType(CellType.STRING);
					str = cell2.getStringCellValue().trim();
					SgbRuleInfo2.setX(str);
					
					// typeO
					cell2 = row2.getCell(10);
					cell2.setCellType(CellType.STRING);
					str = cell2.getStringCellValue().trim();
					SgbRuleInfo2.setTypeO(str);

					list.add(SgbRuleInfo2);
				}
				// }

				// 读取第三章表格内容
				XSSFSheet sheet3 = xwb.getSheetAt(2);
				XSSFRow row3 = null;
				XSSFCell cell3 = null;
				for (int i = 2; i <= sheet3.getPhysicalNumberOfRows(); i++) {
					row3 = sheet3.getRow(i);
					if (row3 == null) {
						continue;
					}
					SgbRuleInfo SgbRuleInfo3 = new SgbRuleInfo();
					String str = "";

					// setSgbAk8
					cell3 = row3.getCell(0);
					// if (cell == null) {
					// continue;
					// }
					cell3.setCellType(CellType.STRING);
					str = cell3.getStringCellValue().trim();
					SgbRuleInfo3.setSgbAk8(str);

					// setSgbAk9
					cell3 = row3.getCell(1);
					cell3.setCellType(CellType.STRING);
					str = cell3.getStringCellValue().trim();
					SgbRuleInfo3.setSgbAk9(str);

					// setSgbAk10
					cell3 = row3.getCell(2);
					cell3.setCellType(CellType.STRING);
					str = cell3.getStringCellValue().trim();
					SgbRuleInfo3.setSgbAk10(str);

					// setSgbAk12
					cell3 = row3.getCell(3);
					cell3.setCellType(CellType.STRING);
					str = cell3.getStringCellValue().trim();
					SgbRuleInfo3.setSgbAk12(str);

					// setSgbAk14
					cell3 = row3.getCell(4);
					cell3.setCellType(CellType.STRING);
					str = cell3.getStringCellValue().trim();
					SgbRuleInfo3.setSgbAk14(str);

					// setSgbAk15
					cell3 = row3.getCell(5);
					cell3.setCellType(CellType.STRING);
					str = cell3.getStringCellValue().trim();
					SgbRuleInfo3.setSgbAk15(str);

					// setSgbAk16
					cell3 = row3.getCell(6);
					cell3.setCellType(CellType.STRING);
					str = cell3.getStringCellValue().trim();
					SgbRuleInfo3.setSgbAk16(str);

					// setSgbAk19
					cell3 = row3.getCell(7);
					cell3.setCellType(CellType.STRING);
					str = cell3.getStringCellValue().trim();
					SgbRuleInfo3.setSgbAk19(str);

					// setSgbAk28
					cell3 = row3.getCell(8);
					cell3.setCellType(CellType.STRING);
					str = cell3.getStringCellValue().trim();
					SgbRuleInfo3.setSgbAk28(str);

					// setSgbAk31
					cell3 = row3.getCell(9);
					cell3.setCellType(CellType.STRING);
					str = cell3.getStringCellValue().trim();
					SgbRuleInfo3.setSgbAk31(str);

					// x
					cell3 = row3.getCell(10);
					cell3.setCellType(CellType.STRING);
					str = cell3.getStringCellValue().trim();
					SgbRuleInfo3.setX(str);
					
					// typeO
					cell3 = row3.getCell(11);
					cell3.setCellType(CellType.STRING);
					str = cell3.getStringCellValue().trim();
					SgbRuleInfo3.setTypeO(str);

					list.add(SgbRuleInfo3);
				}
//			}
			xwb.close();
			return list;
		} catch (NumberFormatException e) {
			e.printStackTrace();
			throw new RuntimeException("excel数据格式错误！");
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			throw new RuntimeException("找不到文件！");
		} catch (IOException e) {
			e.printStackTrace();
			throw new RuntimeException("找不到文件！");
		}
	}
}